﻿<DataListSpec 
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes" 
	ID="efd307e8-6cab-488c-9795-c33a5a1391f1" 
	Name="AddressesForConstituent Data List"
	Description="Return Address Records for Constituent" 
	Author="Blackbaud Product Development"
	common:SecurityUIFolder="Service Bus Adapter">

	<!-- define the SP that returns the data for the datalist -->
	<SPDataList SPName="USP_DATALIST_ADDRESSESFORCONSTITUENT">
		<common:CreateProcedureSQL>
			<![CDATA[
create procedure dbo.USP_DATALIST_ADDRESSESFORCONSTITUENT(
  @CONSTITUENTID uniqueidentifier)
as
	set nocount on;
  
SELECT
  a.ID,
	a.CONSTITUENTID,
	at.DESCRIPTION  as ADDRESSTYPE,
	s.DESCRIPTION   as STATEFULLNAME,
	s.ABBREVIATION  as STATEABBREVIATEDNAME,
	c.DESCRIPTION   as COUNTRYFULLNAME, 
	c.ISO3166  as COUNTRYABBREVIATEDNAME, 
	a.ISPRIMARY, 
	a.STARTDATE, 
	a.ENDDATE, 
	a.ADDRESSBLOCK, 
	a.CITY, 
	a.POSTCODE, 
  a.DONOTMAIL,  
	a.ISCONFIDENTIAL,
  COALESCE(ax.LOOKUPID, ''),
  a.DATEADDED,
  a.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME

  
FROM ADDRESS a
	LEFT OUTER JOIN COUNTRY         c   ON a.COUNTRYID          = c.ID
	LEFT OUTER JOIN STATE           s   ON a.STATEID            = s.ID
	LEFT OUTER JOIN ADDRESSTYPECODE at  ON a.ADDRESSTYPECODEID  = at.ID
  LEFT JOIN USR_ADDRESS           ax  ON a.ID                 = ax.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID                = a.CHANGEDBYID
WHERE a.CONSTITUENTID = @CONSTITUENTID
  
  

			]]>
		</common:CreateProcedureSQL>
	</SPDataList>

  <!-- describe any parameters (other than the context ID) defined on the SP -->
  <Parameters>
    <common:FormMetaData>
      <common:FormFields>
        <common:FormField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid"/>
      </common:FormFields>
    </common:FormMetaData>
  </Parameters>

  <!-- define the output fields in the list -->
  <Output>
    <OutputFields>
      <!-- 0 -->
      <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
      <!-- 1 -->
      <OutputField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid" />
      <!-- 2 -->
      <OutputField FieldID="ADDRESSTYPE" Caption="Address Type" DataType="String" />
      <!-- 3 -->
      <OutputField FieldID="STATEFULLNAME" Caption="State Full Name" DataType="String" />
      <!-- 4 -->
      <OutputField FieldID="STATEABBREVIATEDNAME" Caption="State Abbreviated Name" DataType="String" />
      <!-- 5 -->
      <OutputField FieldID="COUNTRYFULLNAME" Caption="Country Full Name" DataType="String" />
      <!-- 6 -->
      <OutputField FieldID="COUNTRYABBREVIATEDNAME" Caption="Country Abbreviated Name" DataType="String" />
      <!-- 7 -->
      <OutputField FieldID="ISPRIMARY" Caption="Is Primary" DataType="Boolean" />
      <!-- 8 -->
      <OutputField FieldID="STARTDATE" Caption="Start Date" DataType="String" />
      <!-- 9 -->
      <OutputField FieldID="ENDDATE" Caption="End Date" DataType="String" />
      <!-- 10 -->
      <OutputField FieldID="ADDRESSBLOCK" Caption="Address Block" DataType="String" />
      <!-- 11 -->
      <OutputField FieldID="CITY" Caption="Ciy" DataType="String" />
      <!-- 12 -->
      <OutputField FieldID="POSTCODE" Caption="Post Code" DataType="String" />
      <!-- 13 -->
      <OutputField FieldID="DONOTMAIL" Caption="Do Not Mail" DataType="Boolean" />
      <!-- 14 -->
      <OutputField FieldID="ISCONFIDENTIAL" Caption="Is Confidential" DataType="Boolean" />
      <!-- 15 -->
      <OutputField FieldID="LOOKUPID" Caption="Lookup ID" DataType="String" />
      <!-- 16 -->
      <OutputField FieldID="DATEADDED" Caption="Date Added" DataType="Date" />
      <!-- 17 -->
      <OutputField FieldID="DATECHANGED" Caption="Date Changed" DataType="Date" />
      <!-- 18 -->
      <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
    </OutputFields>
  </Output>
</DataListSpec>
